/**
 * 
 */
package com.scpii.universal.hibernate.dao.impl;

import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.ResourceBundle;

import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import com.scpii.universal.constant.GenericConstants;
import com.scpii.universal.dto.FeedBackDto;
import com.scpii.universal.hibernate.dao.FeedbackDao;
import com.scpii.universal.hibernate.entity.FeedbackTab;

/**
 * genericWeb
 * @author weny
 * 2012-7-6上午10:58:19
 */
@Repository
public class FeedbackDaoImpl extends BaseDaoImpl<FeedbackTab, Integer> implements FeedbackDao {

	
	public List<FeedBackDto> geFeedBackList(FeedBackDto condition,
			Integer pageNo, Integer pageSize) {
		ResourceBundle bundle = ResourceBundle.getBundle(GenericConstants.GLOBAL_PROPERTIES_PATH, Locale.getDefault());
		String pnsSchema = bundle.getString("pns.schema");
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT f.*,u.USER_AILAS,u.PHONE_NUMBER from FEEDBACK_TAB f,"+pnsSchema+".USERS u");
		populateSql(condition, sql);
		
		SQLQuery userSqlQuery = getSession().createSQLQuery(sql.toString());
		if (pageNo != 0 && pageSize != 0) {
			userSqlQuery.setFirstResult((pageNo - 1) * pageSize);
			userSqlQuery.setMaxResults(pageSize);
		}
		userSqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		userSqlQuery.setParameter("intDeleteSign", GenericConstants.DELETE_SIGN_EXIST);
		userSqlQuery.setParameter("intParentFeedbackId", GenericConstants.FEED_BACK_PARENT_ID);
		userSqlQuery.setParameter("intAppId", condition.getIntAppId());
		
		List<Map<String, Object>> users = userSqlQuery.list();
		return populateFeedBack(users);
	}

	private List<FeedBackDto> populateFeedBack(List<Map<String, Object>> users) {
		if(users!=null){
			List<FeedBackDto> results = new ArrayList<FeedBackDto>();
			FeedBackDto feed = null;
			for (Map<String, Object> user : users) {
				feed = new FeedBackDto();
				feed.setIntAdminId(Long.parseLong(user.get("INT_ADMIN_ID").toString()));
				feed.setIntFeedbackId(Integer.parseInt(user.get("INT_FEEDBACK_ID").toString()));
				feed.setIntParentFeedbackId(Integer.parseInt(user.get("INT_PARENT_FEEDBACK_ID").toString()));
				feed.setIntUserId(Long.parseLong(user.get("INT_USER_ID").toString()));
				feed.setIntAppId(Integer.parseInt(user.get("INT_APP_ID").toString()));
				feed.setVarFeedbackTitle(nullStringProcess(user.get("VAR_FEEDBACK_TITLE")));
				feed.setVarFeedbackContent(nullStringProcess(user.get("VAR_FEEDBACK_CONTENT")));
				feed.setDatFeedbackDatetime(nullStringProcess(user.get("DAT_FEEDBACK_DATETIME")));
				feed.setIntFeedbackType(Integer.parseInt(user.get("INT_FEEDBACK_TYPE").toString()));
				feed.setIntReplyStatus(Integer.parseInt(user.get("INT_REPLY_STATUS").toString()));
				feed.setDatReplyDatetime(nullStringProcess(user.get("DAT_REPLY_DATETIME")));
				feed.setDatUpdateDatetime(nullStringProcess(user.get("DAT_UPDATE_DATETIME")));
				feed.setVarUserName(nullStringProcess(user.get("USER_AILAS")));
				feed.setVarUserPhoneNum(nullStringProcess(user.get("PHONE_NUMBER")));
				results.add(feed);
			}
			return results;
		}
		return null;
	}

	private void populateSql(FeedBackDto condition, StringBuffer sql) {
		sql.append(" where f.INT_USER_ID=u.ID");
		sql.append(" and f.INT_DELETE_SIGN=:intDeleteSign ");
		sql.append(" and f.INT_PARENT_FEEDBACK_ID=:intParentFeedbackId ");
		sql.append(" and f.INT_APP_ID =:intAppId");
		// 查询条件
		if (condition != null) {
			// 留言人
			if (condition.getVarUserName() != null
					&& !"".equals(condition.getVarUserName())) {
				sql.append(" and u.USER_AILAS like '%" + condition.getVarUserName()+ "%'");
			}
			// 留言内容
			if (condition.getVarFeedbackContent() != null
					&& !"".equals(condition.getVarFeedbackContent())) {
				sql.append(" and f.VAR_FEEDBACK_CONTENT like '%" + condition.getVarFeedbackContent() + "%'");
			}
		}
	}
	
	private String nullStringProcess(Object value) {
		if(value==null)
			return "";
		else if("null".equalsIgnoreCase(value.toString())){
			return "";
		} else
			return value.toString();
	}

	@Override
	public Integer getCountByCondition(FeedBackDto condition) {
		ResourceBundle bundle = ResourceBundle.getBundle(GenericConstants.GLOBAL_PROPERTIES_PATH, Locale.getDefault());
		String pnsSchema = bundle.getString("pns.schema");
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT f.INT_FEEDBACK_ID from FEEDBACK_TAB f,"+pnsSchema+".USERS u");
		populateSql(condition, sql);
		SQLQuery userSqlQuery = getSession().createSQLQuery(sql.toString());
		userSqlQuery.setResultTransformer(Transformers.TO_LIST);
		userSqlQuery.setParameter("intDeleteSign", GenericConstants.DELETE_SIGN_EXIST);
		userSqlQuery.setParameter("intParentFeedbackId", GenericConstants.FEED_BACK_PARENT_ID);
		userSqlQuery.setParameter("intAppId", condition.getIntAppId());
		
		List users = userSqlQuery.list();
		if(users!=null && users.size() > 0)
			return users.size();
		return 0;
	}

	@Override
	public List<FeedBackDto> getFeedBackDetailInfo(Integer intFeedbackId,
			Long intUserId, Integer intAppId) {
		ResourceBundle bundle = ResourceBundle.getBundle(GenericConstants.GLOBAL_PROPERTIES_PATH, Locale.getDefault());
		String pnsSchema = bundle.getString("pns.schema");
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT f.*,u.USER_AILAS,u.PHONE_NUMBER from FEEDBACK_TAB f,"+pnsSchema+".USERS u");
		sql.append(" where f.INT_USER_ID=u.ID");
		sql.append(" and ( f.INT_PARENT_FEEDBACK_ID=:intFeedbackId  ");
		sql.append(" or f.INT_FEEDBACK_ID=:intFeedbackId )  ");
		sql.append(" and f.INT_DELETE_SIGN=:intDeleteSign ");
		SQLQuery userSqlQuery = getSession().createSQLQuery(sql.toString());
		userSqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		userSqlQuery.setParameter("intDeleteSign", GenericConstants.DELETE_SIGN_EXIST);
		userSqlQuery.setParameter("intFeedbackId", intFeedbackId);
		List<Map<String, Object>> users = userSqlQuery.list();
		return populateFeedBack(users);
	}
}
